using System;
using System.IO;
using System.Text;
using System.ComponentModel;
using CodeSmith.Engine;
using System.Data;
using System.Windows.Forms;
using SchemaExplorer;
using CodeSmith.CustomProperties;
using System.Text.RegularExpressions;

namespace CodeSmith.MyBaseTemplates
{
	public class SqlCodeTemplate : CodeTemplate
	{			
		// Namespace.
		private string _Namespace = "Components";
		[Category("Class")]
		[Description("Namespace.")]
		public string Namespace
		{ 
			get {return this._Namespace;}
			set {_Namespace = value;}			
		}

		private string _FolderName = @"D:\CodeSmith.Output";
		// Folder.
		[Category("Database")]
		[Description("Source table.")]
		public string FolderName
		{
			get {return _FolderName;}
			set {_FolderName= value;}
		}
		
		// Source table.
		private TableSchemaCollection _SourceTables;
		[Category("Database")]
		[Description("Source table.")]
		public TableSchemaCollection SourceTables
		{ 
			get {return this._SourceTables;}
			set {_SourceTables = value;}			
		}
		
		// AutoExecuteScript.
		private bool _AutoExecuteScript = false;
		[Category("Database")]
		[Description("Auto Execute Script.")]
		public bool AutoExecuteScript
		{ 
			get {return this._AutoExecuteScript;}
			set {_AutoExecuteScript = value;}			
		}
        
		// Table prefix.
		private string _TablePrefix = "t_";
		[Category("Database")]
		[Description("Table prefix.")]
		public string TablePrefix
		{ 
			get {return this._TablePrefix;}
			set {_TablePrefix = value;}			
		}
		// Stored procedure prefix.
		private string _StoredProcedurePrefix = "p_";
		[Category("Database")]
		[Description("Stored procedured prefix.")]
		public string StoredProcedurePrefix
		{ 
			get {return this._StoredProcedurePrefix;}
			set {_StoredProcedurePrefix = value;}			
		}
		
		
		// Excluded columns.
		private StringCollection _ExcludedColumns;
		[Category("Database")]
		[Description("Excluded Columns.")]
		[Optional()]
		public StringCollection ExcludedColumns
		{ 
			get {return this._ExcludedColumns;}
			set {_ExcludedColumns = value;}
		}
		
		// Included collection.
		private bool _IncludedCollection;
		[Category("Database")]
		[Description("Included Collection.")]
		[Optional()]
		public bool IncludedCollection
		{ 
			get {return this._IncludedCollection;}
			set {_IncludedCollection = value;}
		}        
        
		// IncludeMaDonVi.
		private bool _IncludeMaDonVi = false;
		[Category("Options")]
		[Description("Bao gồm tham số MaDonVi trong các methods và stores")]
		public bool IncludeMaDonVi
		{ 
			get {return this._IncludeMaDonVi;}
			set {_IncludeMaDonVi = value;}			
		}
		
		public void RenderToFile()
		{
			foreach (TableSchema table in this._SourceTables )
			{
				GenerateEntityBaseClass(table, "EntityClass.cst").RenderToFile(FolderName + "\\" + this.GetEntityName(table) + ".generated.cs", true);;
				//GenerateCollectionClass("Collection.cst").RenderToFile(FolderName + "\\" + this.GetEntityName() + "Collection.generated.cs", true);
				/*
				
				GenerateEntityBaseClass("EntityBaseClassWithoutCollection.cst").RenderToFile(FolderName + "\\" + this.GetEntityName() + "Base.cs", true);
				GenerateSQLStoredProcedure("Stored Procedures.cst").RenderToFile(FolderName + "\\" + this.GetEntityName() + "Base.sql", true);
				
				CodeSmith.BaseTemplates.ScriptUtility.ExecuteScript(this.SourceTable.Database.ConnectionString, GenerateSQLStoredProcedure("Stored Procedures.cst").RenderToString());
				*/
				GenerateSQLStoredProcedure(table, "Stored Procedures.cst").RenderToFile(FolderName + "\\SqlScripts\\" + this.GetEntityName(table) + ".generated.sql", true);
				//CodeSmith.BaseTemplates.ScriptUtility.ExecuteScript(table.Database.ConnectionString, GenerateSQLStoredProcedure(table, "Stored Procedures.cst").RenderToString());				
			}
			MessageBox.Show("Completed!");
		}
		public override void Render(TextWriter writer)
		{
			this.RenderToFile();	
			
		}		
		public CodeTemplate GenerateSQLStoredProcedure(TableSchema table, string file)
		{
			CodeTemplate template = GetCodeTemplateInstance(file);
			if (template == null) return null;
		
			CopyPropertiesTo(template);
            template.SetProperty("AutoExecuteScript", this.AutoExecuteScript);
            template.SetProperty("IncludeMaDonVi", this.IncludeMaDonVi);
			template.SetProperty("SourceTable", table);
			template.SetProperty("StoredProcedurePrefix", StoredProcedurePrefix);
			template.SetProperty("TablePrefix", TablePrefix);
			template.SetProperty("IncludeSelectByForeignKey", "True");
			template.SetProperty("IncludeDeleteByForeignKey", "True");
			template.SetProperty("ExcludedColumns", this.ExcludedColumns);
			template.Render(Response);
			return template;						
		}
		
		public CodeTemplate GenerateEntityBaseClass(TableSchema table, string file)
		{
			CodeTemplate template = GetCodeTemplateInstance(file);
			if (template == null) return null;
		
			CopyPropertiesTo(template);
            template.SetProperty("IncludeMaDonVi", this.IncludeMaDonVi);
			template.SetProperty("SourceTable", table);
			template.SetProperty("ExcludedColumns", this.ExcludedColumns);
			template.SetProperty("IncludedCollection", this.IncludedCollection);
			template.Render(Response);		
			return template;			
		}
		
		public string GetEntityName(TableSchema table)
		{
			string entityName = table.Name;
            /*
			int pos = table.Name.LastIndexOf('_');
			if (pos > 0)
			{
				entityName = table.Name.Substring(pos + 1);
			}
			*/
			if (entityName.StartsWith(TablePrefix))
			{
				entityName = entityName.Substring(TablePrefix.Length);
			}
						
			if (entityName.EndsWith("Movies"))
			{
				entityName = entityName.Substring(0, entityName.Length - 3);
				entityName += "ie";
			}

			else if (entityName.EndsWith("Status"))
			{
				entityName = entityName.Substring(0, entityName.Length - 3);
				entityName += "tus";
			}

			else if (entityName.EndsWith("ies"))
			{
				entityName = entityName.Substring(0, entityName.Length - 3);
				entityName += "y";
			}
			else if (entityName.EndsWith("s"))
			{
				entityName = entityName.Substring(0, entityName.Length - 1);
			}
			

			return entityName;
		}		
		
		public string GetStoredProcedurePrefix(TableSchema table)
		{
			//int pos = table.Name.LastIndexOf('_');
			//return table.Name.Substring(0, pos + 1);
            
            return StoredProcedurePrefix;
		}
	}
}